package com.zx.oa.dao.job;

import com.zx.oa.dao.BaseDao;
import com.zx.oa.entity.CheckJob;
import com.zx.oa.entity.Job;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class JobDaoImpl extends BaseDao implements JobDao {

    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    @Override
    public List<Job> getJobList(String queryMajor, String queryClassName, int currentPageNo, int pageSize) {

        List<Job> jobList = new ArrayList<>();
        //可变长度字符串
        StringBuffer sb = new StringBuffer("SELECT zs.`studentId`, zm.`major`, zc.`className`, zs.`studentName`\n" +
                "FROM ztestudent zs\n" +
                "LEFT JOIN zteclassinfo zc ON zc.`classId` = zs.`classId`\n" +
                "LEFT JOIN ztemajor zm ON zm.`id` = zc.`studyType`\n" +
                "LEFT JOIN ztecheckjob zj ON zj.`studentNo` = zs.`studentId`\n" +
                "WHERE 1=1\n" +
                "AND zj.`ckstatu` IS NULL");
        conn = this.getConnection();
        //按照顺序给？拼接参数的值
        List<Object>params=new ArrayList<>();
        if(null!=queryMajor&&!"".equals(queryMajor)){
            sb.append(" AND zm.`major` = ?");
            params.add(queryMajor);
        }
        if(null!=queryClassName&&!"".equals(queryClassName)){
            sb.append(" AND zc.`className` = ?");
            params.add(queryClassName);
        }
        sb.append(" LIMIT ?,?");
        params.add((currentPageNo-1)*pageSize);
        params.add(pageSize);

        try {
            pstmt = conn.prepareStatement(sb.toString());
            //依次给?参数赋值
            for(int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int studentId = rs.getInt("studentId");
                String major = rs.getString("major");
                String className = rs.getString("className");
                String studentName = rs.getString("studentName");

                Job job = new Job(studentId,major,className,studentName);
                jobList.add(job);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn,pstmt,rs);
        }
        return jobList;
    }

    @Override
    public int getJobTotalCount(String queryMajor, String queryClassName) {

        int count=-1;//总记录数
        //可变长度字符串
        StringBuffer sb = new StringBuffer("SELECT COUNT(*)\n" +
                "FROM ztestudent zs\n" +
                "LEFT JOIN zteclassinfo zc ON zc.`classId` = zs.`classId`\n" +
                "LEFT JOIN ztemajor zm ON zm.`id` = zc.`studyType`\n" +
                "LEFT JOIN ztecheckjob zj ON zj.`studentNo` = zs.`studentId`\n" +
                "WHERE 1=1\n" +
                "AND zj.`ckstatu` IS NULL");
        conn = this.getConnection();
        //按照顺序给？拼接参数的值
        List<Object>params=new ArrayList<>();
        if(null!=queryMajor&&!"".equals(queryMajor)){
            sb.append(" AND zm.`major` = ?");
            params.add(queryMajor);
        }
        if(null!=queryClassName&&!"".equals(queryClassName)){
            sb.append(" AND zc.`className` = ?");
            params.add(queryClassName);
        }
        try {
            pstmt = conn.prepareStatement(sb.toString());
            //依次给?参数赋值
            for(int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                count=rs.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return count;
    }

    @Override
    public List<Job> getJobMajorList() {

        List<Job> jobList = new ArrayList<>();
        //可变长度字符串
        StringBuffer sb = new StringBuffer("SELECT zm.`major`\n" +
                "FROM ztestudent zs\n" +
                "LEFT JOIN zteclassinfo zc ON zc.`classId` = zs.`classId`\n" +
                "LEFT JOIN ztemajor zm ON zm.`id` = zc.`studyType`\n" +
                "GROUP BY zm.`major`");
        conn = this.getConnection();
        try {
            pstmt = conn.prepareStatement(sb.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String major = rs.getString("major");
                String className = null;

                Job job = new Job(major,className);
                jobList.add(job);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn,pstmt,rs);
        }
        return jobList;
    }

    @Override
    public List<Job> getJobClassNameList() {

        List<Job> workList = new ArrayList<>();
        //可变长度字符串
        StringBuffer sb = new StringBuffer("SELECT zc.`className`\n" +
                "FROM ztestudent zs\n" +
                "LEFT JOIN zteclassinfo zc ON zc.`classId` = zs.`classId`\n" +
                "LEFT JOIN ztemajor zm ON zm.`id` = zc.`studyType`\n" +
                "GROUP BY zc.`className`");
        conn = this.getConnection();
        try {
            pstmt = conn.prepareStatement(sb.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String major = null;
                String className = rs.getString("className");

                Job job = new Job(major,className);
                workList.add(job);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn,pstmt,rs);
        }
        return workList;
    }

    @Override
    public boolean upsetJob(CheckJob checkJob) {
        String sql="INSERT INTO ztecheckjob(studentNo,ckTime,ckstatu)\n" +
                "VALUES(?,?,?);";
        Object [] param={
                checkJob.getStudentNo(),
                checkJob.getCkTime(),
                checkJob.getCkStatu()
        };
        if(this.executeUpdateInfo(sql,param)>0){
            return  true;
        }
        return false;
    }

}
